﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QL_DTTN.DAL;
namespace QL_DTTN.DTO.DTO
{
    public partial class PhongThi
    {
        public DataTable dataTable(long maDiemThi)
        {
            string sql = " select * from " + _tableName + " where MaDiemThi=@pMaDiemThi ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaDiemThi", maDiemThi));
            return dp.ExDataTable(sql, true);
        }
        public long TongSo()
        {
            string sql = " select COUNT(MaPhongThi) from " + _tableName;
            SqlDataProvider dp = new SqlDataProvider();
            object kq = 0;
            dp.RunReturnSQL(sql, ref kq, true);
            return Convert.ToInt64(kq);
        }
        public DataTable dataSoLuong(long maKyThi)
        {
            string sql = @" select T1.MaTinh,T1.TenTinh,T1.SoThiSinh,T2.SoPhongThi from (
                                select Tinh.maTinh,Tinh.tenTinh ,COUNT(T.soBaoDanh)as 'SoThiSinh' from 
                                Tinh left join Truong on  Tinh.maTinh = Truong.maTinh
                                left join (select * from ThiSinh 
                                where ThiSinh.maKyThi=@pMaKyThi)T on T.maTruong=Truong.maTruong
                                group by Tinh.maTinh,Tinh.tenTinh)T1
                                inner join
                                (select Tinh.maTinh ,COUNT(PhongThi.maPhongThi)as 'SoPhongThi' from 
                                Tinh left join  DiemThi on Tinh.maTinh=DiemThi.maTinh 
                                left  join PhongThi on PhongThi.maDiemThi=DiemThi.maDiemThi
                                group by Tinh.maTinh) T2 on T1.maTinh=T2.maTinh";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaKyThi", maKyThi));
            return dp.ExDataTable(sql, true);
        }
        public List<PhongThi> lstPhongThi(long maDiemThi)
        {
            bool kt = false;
            string sql = " select MaDiemThi,TenPhongThi,MaPhongThi from " + _tableName + " where MaDiemThi=@pMaDiemThi ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaDiemThi", maDiemThi));
            System.Data.SqlClient.SqlDataReader sqlRr = dp.ExDataReader(sql, true);
            List<PhongThi> lstPhongThi = new List<PhongThi>();
            if (sqlRr != null)
            {
                while (sqlRr.Read())
                {
                    PhongThi dt = new PhongThi();
                    if (!Convert.IsDBNull(sqlRr["MaDiemThi"])) dt.MaDiemThi = Convert.ToInt64(sqlRr["MaDiemThi"]);
                    if (!Convert.IsDBNull(sqlRr["TenPhongThi"])) dt.TenPhongThi = sqlRr["TenPhongThi"].ToString();
                    if (!Convert.IsDBNull(sqlRr["MaPhongThi"])) dt.MaPhongThi = Convert.ToInt64(sqlRr["MaPhongThi"]);
                    kt = true;
                    lstPhongThi.Add(dt);
                }
                dp.CloseConnection();
                if (kt) return lstPhongThi;
                else return new List<PhongThi>();
            }
            else
            {
                return new List<PhongThi>();
            }
        }
        public List<PhongThi> lstPhongThi(short maTinh)
        {
            bool kt = false;
            string sql = " select P.MaDiemThi,TenPhongThi,MaPhongThi from " + _tableName + " P inner join DiemThi on DiemThi.MaDiemThi=P.MaDiemThi where MaTinh=@pMaTinh ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaTinh", maTinh));
            System.Data.SqlClient.SqlDataReader sqlRr = dp.ExDataReader(sql, true);
            List<PhongThi> lstPhongThi = new List<PhongThi>();
            if (sqlRr != null)
            {
                while (sqlRr.Read())
                {
                    PhongThi dt = new PhongThi();
                    if (!Convert.IsDBNull(sqlRr["MaDiemThi"])) dt.MaDiemThi = Convert.ToInt64(sqlRr["MaDiemThi"]);
                    if (!Convert.IsDBNull(sqlRr["TenPhongThi"])) dt.TenPhongThi = sqlRr["TenPhongThi"].ToString();
                    if (!Convert.IsDBNull(sqlRr["MaPhongThi"])) dt.MaPhongThi = Convert.ToInt64(sqlRr["MaPhongThi"]);
                    kt = true;
                    lstPhongThi.Add(dt);
                }
                dp.CloseConnection();
                if (kt) return lstPhongThi;
                else return new List<PhongThi>();
            }
            else
            {
                return new List<PhongThi>();
            }
        }
        public DataTable dataMaPhongThi(short maTinh)
        {
            string sql = " select MaPhongThi from " + _tableName + " P inner join DiemThi on DiemThi.MaDiemThi=P.MaDiemThi where MaTinh=@pMaTinh ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaTinh", maTinh));
            return dp.ExDataTable(sql, true);
        }
        public PhongThi Them(PhongThi phongThi)
        {
            string str = " insert into PhongThi (MaDiemThi,TenPhongThi) values (@pMaDiemThi,@pTenPhongThi) select @@IDENTITY ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pTenPhongThi", phongThi.TenPhongThi));
            dp.AddParameter(new SqlParameter("@pMaDiemThi", phongThi.MaDiemThi));
            object kq = 0;
            dp.RunReturnSQL(str, ref kq, true);
            if (Convert.ToInt64(kq) > 0)
            {
                phongThi.MaPhongThi = Convert.ToInt64(kq);
                return phongThi;
            }
            else return null;
        }
    }
}
